package com.wondersgroup.fjzlpt.org.dao.impl.jtqy;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import com.wondersgroup.fjzlpt.org.util.ControllerCommonUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.SqlBeanBaseJTDaoImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.quick.framework.util.FormatDate;
import com.wondersgroup.fjzlpt.org.dao.inter.jtqy.QyxxDAO;

/**
 * 签约信息DAO实现类
 * 
 * @author 01053853
 * 
 */
@Repository
public class QyxxDAOImpl extends SqlBeanBaseJTDaoImpl implements QyxxDAO {

	@Autowired
	private DialectProxy dialectProxy;

	@Override
	@Autowired
	@Qualifier("TB_JTQY_QYXX")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}

	/**
     * 根据条件查询所有
     * @param map
     * @return
     */
	public JSONArray queryAll(Map<String,String> map){
		Map paramObject = bulidQryScript(map);
		List sqlParams = (List) paramObject.get("params");
		String sql = paramObject.get("sql").toString();
		
		SQLBean bean = new SQLBean(sql, sqlParams.toArray());
		return super.queryList(bean);
		
	}
	
	/**
	 * 分页查询签约信息数据
	 * 
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryPage(Map<String, String> map) {
		// sbud.toString(), "order by t.QYXXID ", list.toArray());
		Map paramObject = bulidQryScript(map);
		List list = (List) paramObject.get("params");
		PageQuerySQLBean sb = checkAndCreatePageQuerySQLBean(map, paramObject
				.get("sql").toString(), "order by t.QYXXID ", list.toArray());
		JSONObject jsonObject = super.queryPage(sb,
				ResultHandlerConstant.JSONARRAY);
		return jsonObject;
	}

	/**
	 * 查询所有(查询视图)
	 * @param map
	 * @return
	 */
	public JSONArray queryAllOnView(Map map){
		JSONObject sqlObj = getSqlOnView(map);
		List sqlParams = sqlObj.getJSONArray("sqlParams");
		String sql = sqlObj.getString("sql") + " and a.QYLX <> '07' order by a.QYSJ desc";

		SQLBean bean = new SQLBean(sql, sqlParams.toArray());
		return super.queryList(bean);
	}

	/**
	 * 分页查询签约信息数据(查询视图)
	 * @param map
	 * @return
	 */
	public JSONObject queryPageOnView(Map map){
		ControllerCommonUtil.pageFormat(map);
		JSONObject sqlObj = getSqlOnViewByCondition(map);
		String sql = sqlObj.getString("sql");
		List list = sqlObj.getJSONArray("sqlParams");

//		sql = sql + " and a.QYLX <> '07' ";
		PageQuerySQLBean sb = checkAndCreatePageQuerySQLBean(map, sql, " order by a.QYSJ desc qyxxPage", list.toArray());
		JSONObject jsonObject = super.queryPage(sb,ResultHandlerConstant.JSONARRAY);

		return jsonObject;
	}

	@Override
	public String queryPageSQLHandler(String sql) {
		if(sql.indexOf("qyxxPage") != -1){ //对应方法 ： queryPageOnView
			StringBuilder newSql = new StringBuilder("select aa.* from v_jtqy_qyxx aa right join (")
					.append(sql.replace("qyxxPage","")).append(") bb on aa.qyxxid = bb.qyxxid order by aa.QYSJ desc \n");
			return newSql.toString();
		}
		return super.queryPageSQLHandler(sql);
	}

	/**
	 * 合成查询视图的Sql
	 * @return
	 */
	private JSONObject getSqlOnViewByCondition(Map<String,String> map){
		//是否启用xx表进行关联查询
		boolean //qyxxFlag    = false,
				jmxxFlag     =  false,
				yljgxxFlag   =  false,
				fwbgxFlag    =  false,
				jmrqflgxFlag =  false;


		//step 1 : TB_JTQY_QYXX 查询条件合成
		StringBuffer conditionBuf = new StringBuffer(); //sql条件容器

		List sqlParams = new ArrayList();
		//签约时间段：开始时间
		String value = map.get("QYSJ");
		if(!StringUtils.isEmpty(value)){
			conditionBuf.append("and a.QYSJ = ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		if(!StringUtils.isEmpty(value = map.get("QYSJ_start")) || !StringUtils.isEmpty(value = map.get("QYKSSJ"))){
			conditionBuf.append("and a.QYSJ >= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		//签约时间段：结束时间
		if(!StringUtils.isEmpty(value = map.get("QYSJ_end")) || !StringUtils.isEmpty(value = map.get("QYJSSJ"))){
			conditionBuf.append("and a.QYSJ <= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		value = map.get("SFXY");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.SFXY = ? ");
			sqlParams.add(value);
		}
		// 签约团队ID (支持同时查询多个团队)
		value = map.get("YLTDID");
		if (!StringUtils.isEmpty(value)) {
			String[] yltdidAry = value.split(",");
			if(yltdidAry.length>1){
				conditionBuf.append("and a.YLTDID in (");
				for (String yltdid : yltdidAry) {
					conditionBuf.append("?,");
					sqlParams.add(yltdid);
				}
				conditionBuf.deleteCharAt(conditionBuf.length()-1).append(") ");
			}else{
				conditionBuf.append("and a.YLTDID = ? ");
				sqlParams.add(yltdidAry[0]);
			}
		}
		// 医疗机构ID
		value = map.get("YLJGID");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append(" and a.YLJGID = ? ");
			sqlParams.add(value);
		}
		// 签约信息ID
		value = map.get("QYXXIDS");
		if (!StringUtils.isEmpty(value)) {
			String[] qyxxIdAry = value.split(",");
			if(qyxxIdAry.length>1){
				conditionBuf.append("and a.QYXXID in (");
				for (String qyxxid : qyxxIdAry) {
					conditionBuf.append("?,");
					sqlParams.add(qyxxid);
				}
				conditionBuf.deleteCharAt(conditionBuf.length()-1).append(") ");
			}else{
				conditionBuf.append("and a.QYXXID = ? ");
				sqlParams.add(qyxxIdAry[0]);
			}
		}
		// 签约类型
		value = map.get("QYLX");
		if (!StringUtils.isEmpty(value)) {
			String[] qylxAry = value.split(",");
			if(qylxAry.length>1){
				conditionBuf.append("and a.QYLX in (");
				for (String qylx : qylxAry) {
					conditionBuf.append("?,");
					sqlParams.add(qylx);
				}
				conditionBuf.deleteCharAt(conditionBuf.length()-1).append(") ");
			}else{
				conditionBuf.append("and a.QYLX = ? ");
				sqlParams.add(qylxAry[0]);
			}
		}
		// 不包含的签约类型(有需求可以改为in排除，当前主要排除05（续约）)
		value = map.get("not_QYLX");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.QYLX <> ? ");
			sqlParams.add(value);
		}
		// 到期时间(结束时间)
		value = map.get("JSSJ");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.JSSJ = ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		//到期时间段：开始时间
		value = map.get("JSSJ_start");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.JSSJ >= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		//到期时间段：结束时间
		value = map.get("JSSJ_end");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.JSSJ <= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		// 解约时间段：开始时间
		value = map.get("JYSJ_start");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.JYSJ >= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}
		// 解约时间段：结束时间
		value = map.get("JYSJ_end");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and a.JYSJ <= ? ");
			sqlParams.add(FormatDate.getDateY_M_D(value));
		}

		//step 2 : TB_JTQY_JMXX 查询条件合成
		//StringBuffer jmxxBuf = new StringBuffer("select JMXXID from TB_JTQY_JMXX b where 1=1 ");
		// 居民姓名
		value = map.get("XM");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and b.XM like ? ");
			sqlParams.add("%" + value + "%");
			jmxxFlag = true;
		}
		// 居民证件号码
		value = map.get("ZJHM");
		if (!StringUtils.isEmpty(value)) {
			String[] zjhms = value.split(",");
			if(zjhms.length > 1){
				conditionBuf.append("and b.ZJHM in (");
				for (String zjhm : zjhms) {
					conditionBuf.append("?,");
					sqlParams.add(zjhm);
				}
				conditionBuf.deleteCharAt(conditionBuf.length()-1).append(") ");
			}else{
				conditionBuf.append("and b.ZJHM = ? ");
				sqlParams.add(zjhms[0]);
			}
			jmxxFlag = true;
		}
		value = map.get("JTZZ");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and b.JTZZ like ? ");
			sqlParams.add("%" + value + "%");
			jmxxFlag = true;
		}

		//step 3 : TB_ZD_YLJGXX 查询条件合成
		// 医疗机构名称(所在医院)
		value = map.get("SZYY");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append(" and c.YLJGMC like ? ");
			sqlParams.add("%" + value + "%");
			yljgxxFlag = true;
		}

		//step 4 : TB_JTQY_FWBGX 查询条件合成
		// 服务包ID
		value = map.get("FWBID");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and d.FWBID = ? ");
			sqlParams.add(value);
			fwbgxFlag = true;
		}

		//step 4 : TB_JTQY_JMRQFLGX 查询条件合成
		// 人群分类代码
		if (!StringUtils.isEmpty(value = map.get("RQFL_XMDM")) || !StringUtils.isEmpty(value = map.get("RQFL"))) {
			conditionBuf.append("and e.RQFLID like ? ");
			sqlParams.add("%"+value+"%");
			jmrqflgxFlag = true;
		}
		// 是否为有效签约
		value = map.get("YXQY");
		if (!StringUtils.isEmpty(value)) {
			conditionBuf.append("and e.RQFLID is not null "); // 符合“老年人”，“高血压”等TB_ZY_RQFL值域的数据为“有效签约”记录
			jmrqflgxFlag = true;
		}
		StringBuffer sqlBuf = new StringBuffer();

		sqlBuf.append("select a.QYXXID from TB_JTQY_QYXX a ");
		if(jmxxFlag){
			sqlBuf.append("left join TB_JTQY_JMXX b on a.JMXXID=b.JMXXID ");
		}
		if(yljgxxFlag){
			sqlBuf.append("left join TB_ZD_YLJGXX c on a.YLJGID=c.PTJGID ");
		}
		if(fwbgxFlag){
			//sqlBuf.append("left join(select QYXXID,LISTAGG(FWBID, ',') within group(order by null) FWBID from TB_JTQY_FWBGX group by QYXXID) d on a.QYXXID=d.QYXXID ");
			sqlBuf.append("left join TB_JTQY_FWBGX d on a.QYXXID=d.QYXXID ");
		}
		if(jmrqflgxFlag){
			sqlBuf.append("left join(select JMXXID,LISTAGG(RQFLID, ',') within group(order by null) RQFLID from TB_JTQY_JMRQFLGX group by JMXXID) e on a.JMXXID=e.JMXXID ");
			//sqlBuf.append("left join TB_JTQY_JMRQFLGX e on a.JMXXID=e.JMXXID ");
		}
		sqlBuf.append("where 1=1 ").append(conditionBuf);

		JSONObject result = new JSONObject();
		result.put("sql",sqlBuf.toString());
		result.put("sqlParams",sqlParams);
		return result;
	}

	/**
	 * 查询总数
	 * @param map
	 * @return
	 */
	public int queryCountOnView(Map map){
		ControllerCommonUtil.pageFormat(map);
		JSONObject sqlObj = getSqlOnView(map);
		String sql = sqlObj.getString("sql");
		List list = sqlObj.getJSONArray("sqlParams");
		SQLBean bean = new SQLBean(sql,list.toArray());
//		return super.queryCount(bean);  //报异常： 结果集已耗尽
		return super.queryList(bean).size();
	}

	/**
	 * 合成查询视图的Sql
	 * @return
	 */
	private JSONObject getSqlOnView(Map<String,String> map){
		JSONObject result = new JSONObject();

			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("select * from V_JTQY_QYXX a where 1=1 \n");

			List sqlParams = new ArrayList();
			//签约时间段：开始时间
			String value = map.get("QYSJ");
			if(!StringUtils.isEmpty(value)){
				sqlBuf.append("and a.QYSJ = ? ");
				sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
			}
			if(!StringUtils.isEmpty(value = map.get("QYSJ_start")) || !StringUtils.isEmpty(value = map.get("QYKSSJ"))){
				sqlBuf.append("and a.QYSJ >= ? ");
				sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
			}
			//签约时间段：结束时间
			if(!StringUtils.isEmpty(value = map.get("QYSJ_end")) || !StringUtils.isEmpty(value = map.get("QYJSSJ"))){
				sqlBuf.append("and a.QYSJ <= ? ");
				sqlParams.add(FormatDate.addDay(FormatDate.getDate("yyyy-MM-dd",value),1,"yyyy-MM-dd"));
			}
			//是否续约
			value = map.get("SFXY");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.SFXY = ? ");
				sqlParams.add(value);
			}
			//是否有效
			value = map.get("SFYX");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.SFYX = ? ");
				sqlParams.add(value);
			}
			// 签约团队ID (支持同时查询多个团队)
			value = map.get("YLTDID");
			if (!StringUtils.isEmpty(value)) {
				String[] yltdidAry = value.split(",");
				sqlBuf.append("and a.YLTDID in (");
				for (String yltdid : yltdidAry) {
					sqlBuf.append("?,");
					sqlParams.add(yltdid);
				}
				sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
			}
			// 居民姓名
			value = map.get("XM");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.XM like ? ");
				sqlParams.add("%" + value + "%");
			}
			// 居民证件号码
			value = map.get("ZJHM");
			if (!StringUtils.isEmpty(value)) {
				String[] zjhms = value.split(",");
				if(zjhms.length > 1){
					sqlBuf.append("and a.ZJHM in (");
					for (String zjhm : zjhms) {
						sqlBuf.append("?,");
						sqlParams.add(zjhm);
					}
					sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
				}else{
					sqlBuf.append("and a.ZJHM = ? ");
					sqlParams.add(zjhms[0]);
				}
			}
			// 居民证件号码
			//value = map.get("NZJHM");
			//if (!StringUtils.isEmpty(value)) {
			//	String[] zjhms = value.split(",");
			//	sqlBuf.append("and a.ZJHM in (");
			//	for (String zjhm : zjhms) {
			//		sqlBuf.append("?,");
			//		sqlParams.add(zjhm);
			//	}
			//	sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
			//	//sqlBuf.append("and a.ZJHM like ? ");
			//	//sqlParams.add(value + "%");
			//}
			// 医疗机构ID
			value = map.get("YLJGID");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append(" and a.YLJGID = ? ");
				sqlParams.add(value);
			}
			// 医疗机构名称(所在医院)
			value = map.get("SZYY");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append(" and a.SZYY like ? ");
				sqlParams.add("%" + value + "%");
			}
			// 服务包ID
			value = map.get("FWBID");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.FWBID like ? ");
				sqlParams.add("%"+ value + "%");
			}
			// 签约信息ID
			value = map.get("QYXXIDS");
			if (!StringUtils.isEmpty(value)) {
				String[] qyxxIdAry = value.split(",");
				sqlBuf.append("and a.QYXXID in (");
				for (String qyxxid : qyxxIdAry) {
					sqlBuf.append("?,");
					sqlParams.add(qyxxid);
				}
				sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
			}
			//--------------------------------------------------------------
			// 签约类型
			value = map.get("QYLX");
			if (!StringUtils.isEmpty(value)) {
				String[] qylxAry = value.split(",");
				sqlBuf.append("and a.QYLX in (");
				for (String qylx : qylxAry) {
					sqlBuf.append("?,");
					sqlParams.add(qylx);
				}
				sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
			}
			// 不包含的签约类型(有需求可以改为in排除，当前主要排除05（续约）)
			value = map.get("not_QYLX");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.QYLX <> ? ");
				sqlParams.add(value);
			}
			// 到期时间(结束时间)
			value = map.get("JSSJ");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ = ? ");
				sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
			}
			//到期时间段：开始时间
			value = map.get("JSSJ_start");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ >= ? ");
				sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
			}
			//到期时间段：结束时间
			value = map.get("JSSJ_end");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ <= ? ");
				sqlParams.add(FormatDate.addDay(FormatDate.getDate("yyyy-MM-dd",value),1,"yyyy-MM-dd"));
			}
			// 解约时间段：开始时间
			value = map.get("JYSJ_start");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JYSJ >= ? ");
				sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
			}
			// 解约时间段：结束时间
			value = map.get("JYSJ_end");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JYSJ <= ? ");
				sqlParams.add(FormatDate.addDay(FormatDate.getDate("yyyy-MM-dd",value),1,"yyyy-MM-dd"));
			}
			// 是否为有效签约
			value = map.get("YXQY");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.RQFLID is not null "); // 符合“老年人”，“高血压”等TB_ZY_RQFL值域的数据为“有效签约”记录
			}
			// 人群分类代码
			if (!StringUtils.isEmpty(value = map.get("RQFL_XMDM")) || !StringUtils.isEmpty(value = map.get("RQFL"))) {
				sqlBuf.append("and a.RQFL like ? ");
				sqlParams.add("%[" + value + "]%");
			}
			result.put("sql",sqlBuf.toString());
			result.put("sqlParams",sqlParams);

		return result;
	}

	/**
	 * 分页查询签约单个对象
	 * 
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryOne(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuilder sql = new StringBuilder("select a.*,b.*,(select XMMC from TB_ZY_XB where XMDM=b.XB) XBMC,");
		// 签约服务包  // 人群分类
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sql.append("(select GROUP_CONCAT(concat(d.FWBMC,case when c.FWBSX=1 then '(个人)' else '(家庭)' end)) from TB_JTQY_FWBGX c left join TB_XXWH_FWBJBXX d on c.FWBID=d.FWBID where c.QYXXID=a.QYXXID) QYFWB,");
			sql.append("(select GROUP_CONCAT(f.XMMC) from TB_JTQY_JMRQFLGX e left join TB_ZY_RQFL f on e.RQFLID=f.XMDM where e.JMXXID=a.JMXXID) RQFL,");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sql.append("(select LISTAGG(concat(d.FWBMC,case when c.FWBSX=1 then '(个人)' else '(家庭)' end),',') WITHIN GROUP(order by null) from TB_JTQY_FWBGX c left join TB_XXWH_FWBJBXX d on c.FWBID=d.FWBID where c.QYXXID=a.QYXXID) QYFWB,");
			sql.append("(select LISTAGG(f.XMMC,',') WITHIN GROUP(order by null) from TB_JTQY_JMRQFLGX e left join TB_ZY_RQFL f on e.RQFLID=f.XMDM where e.JMXXID=a.JMXXID) RQFL,");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		// 签约团队名称
		sql.append("(select TDMC from TB_TDGL_TDJBXX where TDID=a.YLTDID) QYTDMC \n");
		sql.append("from TB_JTQY_QYXX a left join TB_JTQY_JMXX b on a.JMXXID=b.JMXXID where 1=1 \n");

		List<String> list = new ArrayList<String>();
		String value = map.get("QYXXID");// 居民信息ID
		if (!StringUtils.isEmpty(value)) {
			sql.append("and a.QYXXID=?");
			list.add(value);
		}
		// 医疗团队id
		value = map.get("YLTDID");
		if (!StringUtils.isEmpty(value)) {
			sql.append("and a.YLTDID=? ");
			list.add(value);
		}
		// 签约类型
		value = map.get("QYLX");
		if (!StringUtils.isEmpty(value)) {
			sql.append("and a.QYLX=? ");
			list.add(value);
		}
		// 签约类型
		value = map.get("ZJHM");
		if (!StringUtils.isEmpty(value)) {
			sql.append("and b.ZJHM=? ");
			list.add(value);
		}

		SQLBean sqlBean = new SQLBean(sql.toString(), list.toArray());
		return super.queryOne(sqlBean);
	}

	/**
	 * 分页查询签约信息对象列表
	 * 
	 * @param map
	 * @return
	 */
	/*@Override
	public JSONArray queryList(Map<String, String> map) {
		Map paramObject = bulidQryScript(map);
		List list = (List) paramObject.get("params");
		SQLBean sb = new SQLBean(paramObject.get("sql").toString(),
				list.toArray());
		return super.queryList(sb);
	}*/

	@Override
	public void updateByCondition(Map map) {
		// TODO Auto-generated method stub
		String qyxxId = (String) map.get("QYXXIDS");
		String qyzt = (String) map.get("QYZT");
		StringBuffer sql = new StringBuffer();
		String[] ids = qyxxId.split(",");
		sql.append("update TB_JTQY_QYXX set QYLX = ? where QYXXID in ( ");
		List<Object> list = new ArrayList<>();
		list.add(qyzt);
		for (String id : ids) {
			sql.append(" ? ,");
			list.add(id);
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" ) ");
		SQLBean sb = new SQLBean(sql.toString(), list.toArray(new Object[list
				.size()]));
		super.update(sb);
	}

	private Map bulidQryScript(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		Map<String, Object> sqlMap = new HashMap<String, Object>();
		List list = new ArrayList();
		String value = null;
		StringBuilder sbud = new StringBuilder();
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sbud.append("SELECT  t.*,GROUP_CONCAT(h.XMMC) RQFLMC ,GROUP_CONCAT(h.XMDM) RQFLID, \n");
			//服务包名称
			sbud.append("(select GROUP_CONCAT(concat(bb.FWBMC,case when aa.FWBSX=1 then '(个人)' else '(家庭)' end)) from TB_JTQY_FWBGX aa,TB_XXWH_FWBJBXX bb where aa.FWBID=bb.FWBID and aa.QYXXID=t.QYXXID) FWBMC, \n");
			sbud.append("(select GROUP_CONCAT(FWBID) from TB_JTQY_FWBGX where QYXXID=t.QYXXID) FWBID \n");
			// t表开始
			sbud.append("FROM (\n ");
			sbud.append("SELECT  a.QYXXID,a.USID,a.YLJGID, a.QYLX, a.SFXY, a.QYSJ,a.SXSJ,");
			sbud.append("a.JSSJ,b.YLJGDM,b.YLJGMC SZYY,g.XM jlry,GROUP_CONCAT(d.FWBSX) FWBSX, ");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sbud.append("SELECT  t.*,LISTAGG(h.XMMC,',') WITHIN GROUP(order by null) RQFLMC ,LISTAGG(h.XMDM,',') WITHIN GROUP(order by null) RQFLID, \n");
			//服务包名称
			sbud.append("(select LISTAGG(concat(bb.FWBMC,case when aa.FWBSX=1 then '(个人)' else '(家庭)' end),',') WITHIN GROUP(order by null) from TB_JTQY_FWBGX aa,TB_XXWH_FWBJBXX bb where aa.FWBID=bb.FWBID and aa.QYXXID=t.QYXXID) FWBMC, \n");
			sbud.append("(select LISTAGG(FWBID,',') WITHIN GROUP(order by null) from TB_JTQY_FWBGX where QYXXID=t.QYXXID) FWBID \n");
			// t表开始
			sbud.append("FROM (\n ");
			sbud.append("SELECT  a.QYXXID,a.USID,a.YLJGID, a.QYLX, a.SFXY, a.QYSJ,a.SXSJ,");
			sbud.append("a.JSSJ,b.YLJGDM,b.YLJGMC SZYY,g.XM jlry,LISTAGG(d.FWBSX,',') WITHIN GROUP(order by null) FWBSX, ");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		

		sbud.append("c.*,F.TDID,F.TDMC  \n");
		sbud.append(" FROM TB_JTQY_QYXX a,TB_ZD_YLJGXX b, \n");
		sbud.append(" TB_JTQY_JMXX c,TB_JTQY_FWBGX d, \n");
		sbud.append(" TB_XXWH_FWBJBXX e, tb_tdgl_tdjbxx F , TB_ZD_YLJGRYXX g \n");
		sbud.append("  where a.YLJGID = b.PTJGID  and a.JMXXID=c.JMXXID \n");
		sbud.append(" and a.USID = g.USERID  \n");
		sbud.append("  AND d.QYXXID=a.QYXXID  \n");
		sbud.append("  AND e.FWBID=d.FWBID \n");
		sbud.append("  AND A.YLTDID=F.TDID \n");
		sbud.append(" AND  a.QYLX = '01' ");
		value = map.get("YLJGID");// 医疗机构ID
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and a.YLJGID = ? ");
			list.add(value);
		}
		value = map.get("XM");// 姓名
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and c.XM like ? ");
			list.add("%" + value + "%");
		}

		value = map.get("ZJHM");// 证件号码
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and c.ZJHM = ? ");
			list.add(value);
		}

		value = map.get("FWBID");// 服务包ID
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and d.FWBID = ? ");
			list.add(value);
		}

		value = map.get("YLTDID");// 证件号码
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and A.YLTDID= ? ");
			list.add(value);
		}
		// value = map.get("QYLX");//证件号码
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYLX= ? ");
		// list.add(value);
		// }
		// value = map.get("QYZT");//证件号码
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYZT= ? ");
		// list.add(value);
		// }
		value = map.get("QYKSSJ");// 签约时间
		if (!StringUtils.isEmpty(value)) {
			Date startDate = FormatDate.getDateY_M_D(value);
			//Date startDate = UtilDateHelper.parse(value, "yyyy-mm-dd");
			sbud.append(" and a.QYSJ >= ? ");
			list.add(value);
		}
		value = map.get("QYJSSJ");// 签约时间
		if (!StringUtils.isEmpty(value)) {
			Date endDate = FormatDate.getDateY_M_D(value);
			//Date endDate = UtilDateHelper.parse(value, "yyyy-mm-dd");
			sbud.append(" and a.QYSJ <= ? ");
			list.add(value);
		}

		// value = map.get("KSSJ");//签约开始时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ > ? ");
		// list.add(value);
		// }
		// value = map.get("JSSJ");//签约结束时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ < ? ");
		// list.add(value);
		// }
		//
		// value = map.get("DQKSSJ");//到期开始时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.JSSJ > ? ");
		// list.add(value);
		// }
		// value = map.get("DQJSSJ");//到期结束时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.JSSJ < ? ");
		// list.add(value);
		// }

		// value = map.get("JYKSSJ");//解约开始时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ > ? ");
		// list.add(value);
		// }
		// value = map.get("JYJSSJ");//解约结束时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ < ? ");
		// list.add(value);
		// }

		// value = map.get("ZJKSSJ");//转介开始时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ > ? ");
		// list.add(value);
		// }
		// value = map.get("ZJJSSJ");//转介结束时间
		// if(!StringUtils.isEmpty(value))
		// {
		// sbud.append(" and a.QYSJ < ? ");
		// list.add(value);
		// }

		value = map.get("QYXXIDS");// 证件号码
		if (!StringUtils.isEmpty(value)) {
			String[] qyxxIdAry = value.split(",");
			if (qyxxIdAry != null && qyxxIdAry.length > 0) {
				sbud.append(" and a.QYXXID in (");
				StringBuffer sql = new StringBuffer();
				for (int i = 0; i < qyxxIdAry.length; i++) {
					sql.append("'" + qyxxIdAry[i] + "'").append(",");
				}
				sql = new StringBuffer(sql.toString().substring(0,
						sql.length() - 1));
				sql = sql.append(" ) ");
				sbud.append(sql.toString());
			}
		}
		
		sbud.append("  GROUP BY a.QYXXID,a.USID,a.YLJGID, a.QYLX, a.SFXY, a.QYSJ,a.SXSJ,a.JSSJ,b.YLJGDM,b.YLJGMC,g.XM,\n");
		sbud.append("c.jmxxid,c.xm,c.zjhm,c.sjhm,c.csrq,c.xb,c.jzkh,c.zjlx,c.jtzz,c.jhrxm,c.yjfgx,c.lxdh,c.sfzh,F.TDID,F.TDMC");
		// t表结束
		sbud.append(" ) t  left join tb_jtqy_jmrqflgx g \n");
		sbud.append(" on  g.JMXXID = t.JMXXID  \n");
		sbud.append(" left join tb_zy_rqfl h \n");
		sbud.append("  on g.RQFLID = h.XMDM where 1 = 1 \n");
		value = map.get("RQFLID");// 人群分类
		if (!StringUtils.isEmpty(value)) {
			sbud.append(" and g.RQFLID= ? ");
			list.add(value);
		}
		sbud.append("  group by t.QYXXID,t.USID,t.YLJGID,t.QYLX,t.SFXY,t.QYSJ,t.SXSJ,t.JSSJ,t.YLJGDM,t.SZYY,t.jlry,t.FWBSX, \n");
		sbud.append("t.jmxxid,t.xm,t.zjhm,t.sjhm,t.csrq,t.xb,t.jzkh,t.zjlx,t.jtzz,t.jhrxm,t.yjfgx,t.lxdh,t.sfzh,t.TDID,t.TDMC  ");
		sqlMap.put("sql", sbud.toString());
		sqlMap.put("params", list);
		return sqlMap;
	}

	/**
	 * 查询已签约数量及其有效签约数量
	 * 
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryQyNumbers(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer sqlBuf = new StringBuffer(
				"select count(QYXXID) YQYRC,sum(case when RQFL is not null then 1 else 0 end ) YXQYRC \n");
		sqlBuf.append("from( \n");
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("select a.QYXXID,GROUP_CONCAT(b.RQFLID) RQFL \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("select a.QYXXID,LISTAGG(b.RQFLID,',') within group(order by null) RQFL \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}
		sqlBuf.append("from TB_JTQY_QYXX a left join TB_JTQY_JMRQFLGX b on a.JMXXID=b.JMXXID \n");
		sqlBuf.append("where a.QYLX='01' \n");

		List<Object> sqlParams = new ArrayList<Object>();
		// 签约时间
		String value = map.get("QYSJ_start");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and a.QYSJ >= ? ");
			sqlParams.add(FormatDate.getDate("yyyy-MM-dd",value));
		}
		value = map.get("QYSJ_end");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and a.QYSJ <= ? ");
			sqlParams.add(FormatDate.addDay(FormatDate.getDate("yyyy-MM-dd",value),1,"yyyy-MM-dd"));
		}
		// 签约团队id
		value = map.get("YLTDID");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and a.YLTDID = ? ");
			sqlParams.add(value);
		}
		sqlBuf.append("group by a.QYXXID) c ");
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());

		return super.queryOne(bean);
	}

	/**
     * 查询所有数据用于'我的签约居民'下载Excel
     * @param map
     * @return
     */
	public JSONArray queryAllForDownload(Map<String, String> map){
		JSONObject sqlObj = this.getQueryGztSql(map);
		
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		SQLBean bean = new SQLBean(sqlObj.getString("sql") + "order by h.QYSJ desc", sqlParams.toArray());
		
		return super.queryList(bean);
	}
	
	/**
     * 分页查询工作台需要的签约数据
     * @param map
     * @return
     */
	@Override
	public JSONObject queryPageForGzt(Map<String, String> map) {
		JSONObject sqlObj = this.getQueryGztSql(map);

//		int pageSize = Integer.parseInt(map.get("rows"));
//		int pageNumber = Integer.parseInt(map.get("page"));
		ControllerCommonUtil.pageFormat(map);

		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlObj.getString("sql"), "order by h.QYSJ desc", sqlParams.toArray());

//		PageQuerySQLBean pageBean = new PageQuerySQLBean(sqlObj.getString("sql"),
//				pageSize, pageNumber, "order by h.QYSJ desc",
//				sqlParams.toArray());

		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}
	
	private JSONObject getQueryGztSql(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		JSONObject result = new JSONObject();
		
		StringBuffer sqlBuf = new StringBuffer();
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("select h.*,GROUP_CONCAT(j.XMMC) RQFLMC \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("select h.*,listagg(j.XMMC,',') within group(order by null) RQFLMC \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sqlBuf.append("from (\n");
		sqlBuf.append("select a.QYXXID,a.QYSJ,a.SXSJ,a.JSSJ,a.QYLX,a.YLTDID,a.JYSJ, \n");
		sqlBuf.append("b.JMXXID,b.XM,b.ZJHM,b.SJHM,b.CSRQ,b.XB,b.JTZZ, \n");
		// 服务包
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("e.yljgmc szyy, GROUP_CONCAT(concat(d.FWBMC,case when c.FWBSX=1 then '(个人)' else '(家庭)' end ),',') QYFWB, \n");
			sqlBuf.append("GROUP_CONCAT(c.fwbid,',') QYFWBIDS, f.tdmc QYTDMC \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("e.yljgmc szyy, listagg(concat(d.FWBMC,case when c.FWBSX=1 then '(个人)' else '(家庭)' end ),',') within group(order by null) QYFWB, \n");
			sqlBuf.append("listagg(c.fwbid,',') within group(order by null) QYFWBIDS, f.tdmc QYTDMC \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sqlBuf.append("from TB_JTQY_QYXX a left join TB_JTQY_JMXX b on a.jmxxid = b.jmxxid \n");
		sqlBuf.append("left join TB_JTQY_FWBGX c on a.qyxxid = c.qyxxid \n");
		sqlBuf.append("left join TB_XXWH_FWBJBXX d on c.fwbid = d.fwbid \n");
		sqlBuf.append("left join TB_ZD_YLJGXX e on a.yljgid = e.ptjgid  \n");
		sqlBuf.append("left join TB_TDGL_TDRY g on a.yltdid = g.tdid    \n");
		sqlBuf.append("left join TB_TDGL_TDJBXX f on g.tdid = f.tdid    \n");
		sqlBuf.append("left join TB_JTQY_JMRQFLGX k on a.JMXXID=k.JMXXID \n");
		sqlBuf.append("where 1=1 \n");

		List<Object> sqlParams = new ArrayList<Object>();
		try {
			// 签约类型
			String value = map.get("QYLX");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.QYLX = ? ");
				sqlParams.add(value);
			}
			value = map.get("USERID");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and g.USERID = ? ");
				sqlParams.add(value);
			}
			// 不包含的签约类型(有需求可以改为in排除，当前主要排除05（续约）)
			value = map.get("not_QYLX");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.QYLX <> ? ");
				sqlParams.add(value);
			}
			// 签约时间
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			value = map.get("QYSJ_start");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.QYSJ >= ? ");
				sqlParams.add(sdf.parse(value));
			}
			value = map.get("QYSJ_end");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.QYSJ <= ? ");
				sqlParams.add(sdf.parse(value));
			}
			// 签约团队id
			value = map.get("YLTDID");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.YLTDID = ? ");
				sqlParams.add(value);
			}
			// 医疗机构id
			value = map.get("YLJGID");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.YLJGID = ? ");
				sqlParams.add(value);
			}
			// 到期时间
			value = map.get("JSSJ");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ = ? ");
				sqlParams.add(sdf.parse(value));
			}
			value = map.get("JSSJ_start");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ >= ? ");
				sqlParams.add(sdf.parse(value));
			}
			value = map.get("JSSJ_end");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JSSJ <= ? ");
				sqlParams.add(sdf.parse(value));
			}
			// 解约时间
			value = map.get("JYSJ_start");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JYSJ >= ? ");
				sqlParams.add(sdf.parse(value));
			}
			value = map.get("JYSJ_end");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and a.JYSJ <= ? ");
				sqlParams.add(sdf.parse(value));
			}
			// 姓名/患者姓名
			if (!StringUtils.isEmpty(value = map.get("XM")) || !StringUtils.isEmpty(value = map.get("HZXM"))) {
				sqlBuf.append("and b.XM like ? ");
				sqlParams.add("%" + value + "%");
			}
			// 患者证件号码
			value = map.get("ZJHM");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and b.ZJHM = ? ");
				sqlParams.add(value);
			}
			// 团队角色
			value = map.get("TDJS");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and g.TDJS = ? ");
				sqlParams.add(value);
			}
			// 是否为有效签约
			value = map.get("YXQY");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and k.RQFLID is not null "); // 符合“老年人”，“高血压”等TB_ZY_RQFL值域的数据为“有效签约”记录
			}
			// 人群分类代码
			/*value = map.get("RQFL_XMDM");
			if (!StringUtils.isEmpty(value)) {
				sqlBuf.append("and k.RQFLID = ? ");
				sqlParams.add(value);
			}*/
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		sqlBuf.append("group by a.QYXXID,a.QYSJ,a.SXSJ,a.JSSJ,a.QYLX,a.YLTDID,a.JYSJ,b.JMXXID,b.XM,b.ZJHM,b.SJHM,b.CSRQ,b.XB,b.JTZZ,e.yljgmc,f.tdmc ) h \n");
		sqlBuf.append("left join TB_JTQY_JMRQFLGX i on h.JMXXID=i.JMXXID \n");
		sqlBuf.append("left join TB_ZY_RQFL j on i.RQFLID=j.XMDM \n");
		sqlBuf.append("group by h.QYXXID,h.QYSJ,h.SXSJ,h.JSSJ,h.QYLX,h.YLTDID,h.JYSJ, h.JMXXID,h.XM,h.ZJHM,h.SJHM,h.CSRQ,h.XB,h.JTZZ,h.QYFWB,h.QYFWBIDS,h.szyy,h.QYTDMC");

		result.put("sql", sqlBuf.toString());
		result.put("sqlParams", sqlParams);
		
		return result;
	}

	@Override
	public JSONArray updateFwbbg(Map<String, String> map) {
		JSONArray bgidArr = new JSONArray();

//		String[] QYXXID = map.get("QYXXIDS").split(",");
//		String[] NEWFWB = map.get("NEWFWBID").split("\\|");
//		String[] OLDFWB = map.get("OLDFWBID").split("\\|");
//		String[] NEWFWBSX = map.get("NEWFWBSX").split("\\|");
//		String[] OLDFWBSX = map.get("OLDFWBSX").split("\\|");
		JSONArray QYXXIDS = JSONArray.parseArray(map.get("QYXXIDS"));
		JSONArray NEWFWBID = JSONArray.parseArray(map.get("NEWFWBID"));
		JSONArray OLDFWBID = JSONArray.parseArray(map.get("OLDFWBID"));
		JSONArray NEWFWBSX = JSONArray.parseArray(map.get("NEWFWBSX"));
		JSONArray OLDFWBSX = JSONArray.parseArray(map.get("OLDFWBSX"));

		StringBuffer sql = new StringBuffer();
		sql.append("delete from  TB_JTQY_FWBGX where QYXXID in ( ");
		List<Object[]> insertNewFwbList = new ArrayList<>();
		List<Object[]> insertOldFwbList = new ArrayList<>();
		List<Object> deleteFWBList = new ArrayList<>();
		for (int i = 0; i < QYXXIDS.size(); i++) {
			sql.append(" ? ,");
			deleteFWBList.add(QYXXIDS.getString(i));
			String fwbbgID = UUID.randomUUID().toString();
			bgidArr.add(fwbbgID);
			String[] fwbxx = NEWFWBID.getString(i).split(",");
			String[] fwbsx = NEWFWBSX.getString(i).split(",");
			String[] oldfwbxx = OLDFWBID.getString(i).split(",");
			String[] oldfwbsx = OLDFWBSX.getString(i).split(",");
			for (int j = 0; j < fwbxx.length; j++) {
				List<Object> newList = new ArrayList<>();
				newList.add(fwbxx[j]);
				newList.add(QYXXIDS.getString(i));
				newList.add(fwbsx[j]);
				insertNewFwbList.add(newList.toArray());
			}
			for (int k = 0; k < oldfwbxx.length; k++) {
				List<Object> oldList = new ArrayList<>();
				oldList.add(fwbbgID);
				oldList.add(oldfwbxx[k]);
				oldList.add(QYXXIDS.getString(i));
				oldList.add(oldfwbsx[k]);
				oldList.add(new Date());
				insertOldFwbList.add(oldList.toArray());
			}
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" ) ");
		SQLBean deleteFWB = new SQLBean(sql.toString(), deleteFWBList.toArray());
		super.update(deleteFWB);
		SQLBean insertNewFWB = new SQLBean(
				"INSERT INTO TB_JTQY_FWBGX VALUES ( ? , ? , ? ) ",
				insertNewFwbList.toArray());
		insertNewFWB.setBatch(true);
		super.update(insertNewFWB);
		SQLBean insertOldFWB = new SQLBean(
				"INSERT INTO TB_JTQY_FWBBG VALUES ( ? , ? , ? , ? , ? ) ",
				insertOldFwbList.toArray());
		insertOldFWB.setBatch(true);
		super.update(insertOldFWB);

		return bgidArr;
	}

	/**
	 * 分页查询待分配任务
	 * 
	 * @param map
	 * @return
	 */
	public JSONObject queryDfprwByPage(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select * from (");
		sqlBuf.append(" select a.QYXXID,a.YLTDID,a.JMXXID,b.FWBSX,c.FWBID,e.XM,d.FWID,d.FWMC,d.FWNR,c.FWPC,e.ZJHM SFZH, \n");
		sqlBuf.append("(select concat(FWBMC,case when b.FWBSX=1 then '(个人)' else '(家庭)' end) from TB_XXWH_FWBJBXX where FWBID=b.FWBID) FWBMC \n");
		sqlBuf.append("from TB_JTQY_QYXX a left join TB_JTQY_FWBGX b on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_XXWH_FWB_FWXX c on b.FWBID=c.FWBID \n");
		sqlBuf.append("left join TB_XXWH_FWXX d on c.FWID=d.FWID \n");
		sqlBuf.append("left join TB_JTQY_JMXX e on a.JMXXID=e.JMXXID \n");

		sqlBuf.append("union all \n");

		/*
		* 查询医保服务项
		* 医保服务项不存在FWBID---当前直接使用FWID代替FWBID
		* */
		sqlBuf.append("select a.QYXXID,a.YLTDID,a.JMXXID,'' as FWBSX,b.FWID as FWBID,d.XM,b.FWID,c.FWMC,c.FWNR,b.FWCS FWPC,d.ZJHM SFZH,'' as FWBMC \n");
		sqlBuf.append("from TB_JTQY_YBFWXGX b left join TB_JTQY_QYXX a on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_XXWH_FWXX c on b.FWID=c.FWID \n");
		sqlBuf.append("left join TB_JTQY_JMXX d on a.JMXXID=d.JMXXID ) t where 1=1 \n");

		List<String> sqlParams = new ArrayList<String>();
		// 签约信息id
		String value = map.get("QYXXIDS");
		if (!StringUtils.isEmpty(value)) {
			String[] QYXXIDS = value.split(",");
			if(QYXXIDS.length==1){
				sqlBuf.append("and t.QYXXID=? ");
				sqlParams.add(value);
			}else{
				sqlBuf.append("and t.QYXXID in (");
				for (String QYXXID : QYXXIDS) {
					sqlBuf.append("?,");
					sqlParams.add(QYXXID);
				}
				sqlBuf.deleteCharAt(sqlBuf.length() - 1).append(") ");
			}
		}
		// 服务名称
		value = map.get("FWMC");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and t.FWMC = ? ");
			sqlParams.add(value);
		}
		// 患者姓名
		value = map.get("XM");
		if(!StringUtils.isEmpty(value)) {
			sqlBuf.append("and t.XM like ? ");
			sqlParams.add("%" + value + "%");
		}
		// 证件号码
		value = map.get("ZJHM");
		if(!StringUtils.isEmpty(value)) {
			sqlBuf.append("and t.SFZH like ? ");
			sqlParams.add("%" + value + "%");
		}

		ControllerCommonUtil.pageFormat(map);
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlBuf.toString(),
				"order by t.QYXXID,t.FWBID,t.FWID",sqlParams.toArray());

		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}

	/**
	 * 查询团队与患者签订的所有服务包
	 * 
	 * @param TDIDs
	 * @return
	 */
	public JSONArray queryFwbByTdid(String[] TDIDs) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select distinct c.FWBMC,c.FWBID from TB_JTQY_QYXX a left join TB_JTQY_FWBGX b on a.QYXXID=b.QYXXID \n");
		sqlBuf.append("left join TB_XXWH_FWBJBXX c on b.FWBID=c.FWBID where 1=1 ");

		List<String> sqlParams = new ArrayList<String>();
		if(TDIDs != null && TDIDs.length>0){
			sqlBuf.append("and a.YLTDID in( ");
			for (String TDID : TDIDs) {
				sqlBuf.append("?,");
				sqlParams.add(TDID);
			}
			sqlBuf.deleteCharAt(sqlBuf.length() - 1).append(")");
		}

		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.queryList(bean);
	}

	@Override
	public boolean isExist(Map<String, String> map) {
		// String fwbid = (String)map.get("FWBID");
		StringBuffer sql = new StringBuffer();
		List<Object> list = new ArrayList<>();
		//sql.append(" select count(1) from tb_jtqy_qyxx A , TB_JTQY_JMXX B \n");
		//sql.append(" where A.JMXXID = B.JMXXID \n");
		//sql.append(" AND (A.QYLX = '01' or A.QYLX = '02' ) AND A.SFYX = '0' \n");
		//if (!StringUtils.isEmpty(map.get("YLJGID"))) {
		//	sql.append(" and A.YLJGID = ? \n");
		//	list.add(map.get("YLJGID"));
		//}
		//if (!StringUtils.isEmpty(map.get("ZJHM"))) {
		//	sql.append(" and B.ZJHM = ? \n");
		//	list.add(map.get("ZJHM"));
		//}
		//if (!StringUtils.isEmpty(map.get("JMXXID"))) {
		//	sql.append(" and B.JMXXID = ? \n");
		//	list.add(map.get("JMXXID"));
		//}
		sql.append(" select count(1) from v_jtqy_qyxx where 1=1 \n");
		sql.append(" and (QYLX = '01' OR QYLX = '02') AND SFYX = '0' \n");
		if (!StringUtils.isEmpty(map.get("YLJGID"))) {
			sql.append(" and YLJGID = ? \n");
			list.add(map.get("YLJGID"));
		}
		if (!StringUtils.isEmpty(map.get("ZJHM"))) {
			String[] zjhms = map.get("ZJHM").split(",");
			sql.append("and ZJHM in (");
			for (String zjhm : zjhms) {
				sql.append("?,");
				list.add(zjhm);
			}
			sql.deleteCharAt(sql.length()-1).append(") ");
			//sql.append(" and ZJHM = ? \n");
			//list.add(map.get("ZJHM"));
		}
		if (!StringUtils.isEmpty(map.get("JMXXID"))) {
			sql.append(" and JMXXID = ? \n");
			list.add(map.get("JMXXID"));
		}
		SQLBean sb = new SQLBean(sql.toString(), list.toArray());
		return super.isExist(sb);
	}
	
	@Override
	public JSONObject isExistYqy(Map<String, String> map) {
		// String fwbid = (String)map.get("FWBID");
		StringBuffer sql = new StringBuffer();
		sql.append(" select A.QYXXID from tb_jtqy_qyxx A , TB_JTQY_JMXX B \n");
		sql.append(" where A.JMXXID = B.JMXXID \n");
		sql.append(" AND (A.QYLX = '00' or A.QYLX = '07' ) AND A.SFYX = '0' \n");
		List<Object> list = new ArrayList<>();
		if (!StringUtils.isEmpty(map.get("YLJGID"))) {
			sql.append(" and A.YLJGID = ? \n");
			list.add(map.get("YLJGID"));
		}
		if (!StringUtils.isEmpty(map.get("ZJHM"))) {
			String[] zjhms = map.get("ZJHM").split(",");
			sql.append(" and B.ZJHM in (");
			for(String zjhm : zjhms){
				sql.append(" ?,");
				list.add(zjhm);
			}
			sql.deleteCharAt(sql.length()-1).append(") \n");
		}
		if (!StringUtils.isEmpty(map.get("JMXXID"))) {
			sql.append(" and A.JMXXID = ? \n");
			list.add(map.get("JMXXID"));
		}
		SQLBean sb = new SQLBean(sql.toString(), list.toArray());
		return super.queryOne(sb);
	}

	@Override
	public JSONObject jtcyQyxxQuery(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer sql = new StringBuffer();
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sql.append(" SELECT A.XM,GROUP_CONCAT(concat(D.FWBMC,CASE WHEN C.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sql.append(" SELECT A.XM,LISTAGG(concat(D.FWBMC,CASE WHEN C.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sql.append(" FWBMC  FROM TB_JTQY_JMXX A LEFT JOIN TB_JTQY_QYXX B  \n");
		sql.append(" ON A.JMXXID = B.JMXXID AND (B.QYLX = '01' OR B.QYLX = '02') \n");
		sql.append(" LEFT JOIN TB_JTQY_FWBGX C ON B.QYXXID = C.QYXXID  \n");
		sql.append(" LEFT JOIN TB_XXWH_FWBJBXX D ON C.FWBID = D.FWBID  \n");
		sql.append(" WHERE A.ZJHM = ? group by a.XM \n");
		SQLBean sb = new SQLBean(sql.toString(),
				new Object[] { map.get("ZJHM") });
		return super.queryOne(sb);
	}

	@Override
	public JSONObject queryALLPage(Map<String, String> map) {
		
		int pageSize = Integer.parseInt(map.get("rows"));
		int pageNumber = Integer.parseInt(map.get("page"));
		
		JSONObject sqlObj = this.getQueryAllSql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		PageQuerySQLBean pageBean = new PageQuerySQLBean(sqlObj.getString("sql"),
				pageSize, pageNumber, " order by T.QYSJ desc ",
				sqlParams.toArray());
		JSONObject result = super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
		return result;
	}
	
	/**
     * 查询数据下载到Excel的数据
     * @param map
     * @return
     */
	public JSONArray queryALLForExcel(Map<String,String> map){
		JSONObject sqlObj = this.getQueryAllSql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		SQLBean bean = new SQLBean(sqlObj.getString("sql") + " order by T.QYSJ desc", sqlParams.toArray());
		
		return super.queryList(bean);
	}
	
	/**
	 * 获取查询sql
	 * @param map
	 * @return
	 */
	private JSONObject getQueryAllSql(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		JSONObject result = new JSONObject();
		
		StringBuffer sqlBuf = new StringBuffer();
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("SELECT T.*,GROUP_CONCAT(h.XMMC) RQFLMC ,GROUP_CONCAT(h.XMDM) RQFLID FROM ( \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("SELECT T.*,LISTAGG(h.XMMC,',') WITHIN GROUP(order by null) RQFLMC ,LISTAGG(h.XMDM,',') WITHIN GROUP(order by null) RQFLID FROM ( \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sqlBuf.append(" SELECT  A.QYXXID,A.QYSJ,A.SXSJ,A.JSSJ,A.ZTMS,A.SFXY,A.QYLX,A.YLTDID TDID,A.YLJGID,A.JYSJ, \n");// QYXXID,QYSJ,QYLX,QYZT,YLTDID,QYTDMC,JMXXID,XM,ZJHM,SJHM,CSRS,XB,JTZZ,RQFLMC,QYFWB,QYFWBIDS
		sqlBuf.append(" (select TDMC from TB_TDGL_TDJBXX where TDID=A.YLTDID) QYTDMC, \n");
		sqlBuf.append(" (select XM from TB_ZD_YLJGRYXX where USERID = A.USID ) QYYS,   \n");
		sqlBuf.append(" (select XM from TB_ZD_YLJGRYXX where USERID = A.JYYH ) JYYS,  \n");
		sqlBuf.append(" (select YLJGMC from TB_ZD_YLJGXX where PTJGID = A.YLJGID ) YLJGMC,  \n");
		sqlBuf.append(" (select xmmc from TB_ZY_ZJYY where xmdm = C.zjyy ) zjyyms,  \n");
		sqlBuf.append(" (select XM from TB_ZD_YLJGRYXX where USERID = C.USERID ) SQYS,  \n");
		sqlBuf.append(" (select XM from TB_ZD_YLJGRYXX where USERID = C.SHYH ) SHYS,  \n");
		sqlBuf.append(" (select TDMC from TB_TDGL_TDJBXX where TDID = C.YQYTD) YQYTDMC,  \n");
		sqlBuf.append("	(select TDMC from TB_TDGL_TDJBXX where TDID = C.XQYTD) XQYTDMC,   \n");// 原团队名称
		sqlBuf.append(" B.*,C.ZJSQID, C.YQYTD,C.XQYTD,C.SQRQ,C.ZJYY,C.SHZT,C.USERID,C.SHYH,C.SHRQ, \n");// 转介团队名称
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append(" GROUP_CONCAT(concat(G.FWBMC,CASE WHEN E.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) FWBMC , \n");
			sqlBuf.append(" GROUP_CONCAT(G.FWBID) FWBID  \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append(" LISTAGG(concat(G.FWBMC,CASE WHEN E.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) FWBMC , \n");
			sqlBuf.append(" LISTAGG(G.FWBID,',') WITHIN GROUP(order by null) FWBID  \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sqlBuf.append(" from TB_JTQY_QYXX A  \n");
		sqlBuf.append(" LEFT JOIN TB_JTQY_JMXX B ON A.JMXXID = B.JMXXID  \n");
		sqlBuf.append(" LEFT JOIN TB_JTQY_ZJSQ C ON A.QYXXID = C.QYXXID AND C.SHZT = '0' \n");
		sqlBuf.append(" LEFT JOIN TB_JTQY_FWBGX E ON A.QYXXID = E.QYXXID  \n");
		//sqlBuf.append(" LEFT JOIN TB_JTQY_JMRQFLGX F ON A.JMXXID = F.JMXXID  \n");
		sqlBuf.append(" LEFT JOIN TB_XXWH_FWBJBXX G ON E.FWBID = G.FWBID  \n");
		sqlBuf.append(" WHERE A.SFYX = '0'  \n");
		List<String> sqlParams = new ArrayList<String>();
		// 签约类型
		String value = map.get("QYLX");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.QYLX = ? ");
			sqlParams.add(value);
		}
		// 签约时间
		value = map.get("QYSJ_start");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.QYSJ >= to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		value = map.get("QYSJ_end");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.QYSJ <= to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		// 签约团队id
		value = map.get("YLTDID");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.YLTDID in ( ");
			String[] tdidArr = value.split(",");
			for (String tdid : tdidArr) {
				sqlBuf.append("?,");
				sqlParams.add(tdid);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(")");
		}
		// 到期时间
		value = map.get("JSSJ");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.JSSJ = to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		// 解约时间
		value = map.get("JYSJ_start");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.JYSJ >= to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		value = map.get("JYSJ_end");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and A.JYSJ <= to_date(?,'yyyy-MM-dd') ");
			sqlParams.add(value);
		}
		// 患者姓名
		value = map.get("XM");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and B.XM like ? ");
			sqlParams.add("%" + value + "%");
		}
		// 患者证件号码
		value = map.get("ZJHM");
		if (!StringUtils.isEmpty(value)) {
			sqlBuf.append("and B.ZJHM = ? ");
			sqlParams.add(value);
		}
		///1111111111111111111111111111111
		sqlBuf.append("group by A.QYXXID,A.QYSJ,A.SXSJ,A.JSSJ,A.ZTMS,A.SFXY,A.QYLX,A.YLTDID,A.YLJGID,A.JYSJ,A.USID,A.JYYH,\n");
		sqlBuf.append("b.jmxxid,b.xm,b.zjhm,b.sjhm,b.csrq,b.xb,b.jzkh,b.zjlx,b.jtzz,b.jhrxm,b.yjfgx,b.lxdh,b.sfzh,\n");
		sqlBuf.append("C.ZJSQID, C.YQYTD,C.XQYTD,C.SQRQ,C.ZJYY,C.SHZT,C.USERID,C.SHYH,C.SHRQ \n");
		sqlBuf.append(" ) t ");
		sqlBuf.append(" LEFT JOIN TB_JTQY_JMRQFLGX F ON t.JMXXID = F.JMXXID  \n");
		sqlBuf.append(" left join tb_zy_rqfl H on F.RQFLID = H.XMDM \n");
		sqlBuf.append(" group by t.QYXXID,t.QYSJ,t.SXSJ,t.JSSJ,t.ZTMS,t.SFXY,t.QYLX,t.TDID,t.YLJGID,t.JYSJ,t.QYTDMC,t.QYYS,t.JYYS, \n");
		sqlBuf.append("t.YLJGMC,t.ZJYYMS,t.SQYS,t.SHYS,t.YQYTDMC,t.XQYTDMC,t.jmxxid,t.xm,t.zjhm,t.sjhm,t.csrq,t.xb,t.jzkh,t.zjlx,\n");
		sqlBuf.append("t.jtzz,t.jhrxm,t.yjfgx,t.lxdh,t.sfzh,t.ZJSQID, t.YQYTD,t.XQYTD,t.SQRQ,t.ZJYY,t.SHZT,t.USERID,t.SHYH,t.SHRQ,t.FWBMC,t.FWBID ");
		
		result.put("sql", sqlBuf.toString());
		result.put("sqlParams", sqlParams);
		return result;
	}

	@Override
	public JSONObject findLsjlAll(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer qylsjl = new StringBuffer();
		qylsjl.append(" select A.SXSJ,A.JSSJ,A.QYLX,A.JYSJ,A.ZTMS,  \n");
		qylsjl.append(" (select YLJGMC from TB_ZD_YLJGXX where PTJGID = A.YLJGID ) YLJGMC,  \n");
		qylsjl.append(" (select TDMC from TB_TDGL_TDJBXX where TDID=A.YLTDID) TDMC, \n");
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			qylsjl.append(" GROUP_CONCAT(concat(C.FWBMC,CASE WHEN B.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) FWBMC   \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			qylsjl.append(" LISTAGG(concat(C.FWBMC,CASE WHEN B.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) FWBMC   \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		qylsjl.append(" from TB_JTQY_QYXX A LEFT JOIN TB_JTQY_FWBGX B ON A.QYXXID = B.QYXXID  \n");
		qylsjl.append(" LEFT JOIN TB_XXWH_FWBJBXX C ON B.FWBID = C.FWBID  \n");
		qylsjl.append(" WHERE (A.QYLX = '03' OR A.QYLX = '04' ) AND A.JMXXID = ?  \n");
		qylsjl.append(" GROUP BY A.QYXXID,A.SXSJ,A.JSSJ,A.QYLX,A.JYSJ,A.ZTMS,A.YLJGID,A.YLTDID");
		SQLBean qylsjlSqlBean = new SQLBean(qylsjl.toString(),
				new Object[] { map.get("JMXXID") });
		
		StringBuffer zjtdjl = new StringBuffer();
		zjtdjl.append(" SELECT B.SXSJ,B.JSSJ,A.SHRQ,(select xmmc from TB_ZY_ZJYY where xmdm = A.zjyy ) zjyyms, \n");
		zjtdjl.append(" (select YLJGMC from TB_ZD_YLJGXX where PTJGID = B.YLJGID ) YLJGMC, \n");
		zjtdjl.append(" (select TDMC from TB_TDGL_TDJBXX where TDID = A.YQYTD) YQYTDMC,  \n");
		zjtdjl.append(" (select TDMC from TB_TDGL_TDJBXX where TDID = A.XQYTD) XQYTDMC,  \n");
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			zjtdjl.append(" GROUP_CONCAT(concat(D.FWBMC,CASE WHEN C.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) FWBMC \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			zjtdjl.append(" LISTAGG(concat(D.FWBMC,CASE WHEN C.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) FWBMC \n");
		}

		zjtdjl.append(" FROM TB_JTQY_ZJSQ A left join  TB_JTQY_QYXX B ON A.QYXXID = B.QYXXID  \n");
		zjtdjl.append(" LEFT JOIN TB_JTQY_FWBGX C ON A.QYXXID = C.QYXXID  \n");
		zjtdjl.append(" LEFT JOIN TB_XXWH_FWBJBXX D ON C.FWBID = D.FWBID  \n");
		zjtdjl.append(" WHERE A.SHZT = '1' AND B.JMXXID = ? \n");
		zjtdjl.append("GROUP BY A.ZJSQID,A.zjyy,B.SXSJ,B.JSSJ,A.SHRQ,B.YLJGID,A.YQYTD,A.XQYTD order by a.SHRQ desc");
		SQLBean zjtdjlSqlBean = new SQLBean(zjtdjl.toString(),
				new Object[] { map.get("JMXXID") });
		
		StringBuffer bgfwbjl = new StringBuffer();
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			bgfwbjl.append(" SELECT GROUP_CONCAT(concat(B.FWBMC,CASE WHEN A.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) YFWBMC , \n");
			bgfwbjl.append(" GROUP_CONCAT(concat(B.FWBMC,CASE WHEN C.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) XFWBMC , \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			bgfwbjl.append(" SELECT LISTAGG(concat(B.FWBMC,CASE WHEN A.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) YFWBMC , \n");
			bgfwbjl.append("        C.XFWBMC , \n");
		}
		bgfwbjl.append(" D.SXSJ,D.JSSJ,A.BGSJ,(select YLJGMC from TB_ZD_YLJGXX where PTJGID = D.YLJGID ) YLJGMC,  \n");
		bgfwbjl.append(" (select TDMC from TB_TDGL_TDJBXX where TDID=D.YLTDID) TDMC  \n");
		bgfwbjl.append(" FROM TB_JTQY_FWBBG A LEFT JOIN TB_XXWH_FWBJBXX B ON A.FWBID = B.FWBID  \n");
		bgfwbjl.append(" LEFT JOIN TB_JTQY_QYXX D ON A.QYXXID = D.QYXXID  \n");
		bgfwbjl.append(" LEFT JOIN (SELECT A.QYXXID,LISTAGG(concat(B.FWBMC,CASE WHEN A.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) XFWBMC \n" +
				" FROM TB_JTQY_FWBGX A LEFT JOIN TB_XXWH_FWBJBXX B ON A.FWBID = B.FWBID \n" +
				"GROUP BY A.QYXXID ) C ON A.QYXXID = C.QYXXID  \n");

//		bgfwbjl.append(" LEFT JOIN TB_JTQY_FWBGX C ON A.QYXXID = C.QYXXID  \n");

//		bgfwbjl.append(" LEFT JOIN TB_XXWH_FWBJBXX E ON C.FWBID = E.FWBID  \n");
		bgfwbjl.append(" where D.JMXXID  = ? \n");
		bgfwbjl.append("group by A.bgid,A.BGSJ,D.SXSJ,D.JSSJ,D.YLJGID,D.YLTDID,C.XFWBMC");
		SQLBean bgfwbSqlBean = new SQLBean(bgfwbjl.toString(),
				new Object[] { map.get("JMXXID") });
		JSONArray qyjlAry = super.queryList(qylsjlSqlBean);
		JSONArray zjtdAry = super.queryList(zjtdjlSqlBean);
		JSONArray bgfwbAry = super.queryList(bgfwbSqlBean);
		JSONObject jo = new JSONObject();
		jo.put("QYLSJL", qyjlAry);
		jo.put("ZJTDJL", zjtdAry);
		jo.put("BGFWBJL", bgfwbAry);
		return jo;
		
	}

	/**
	 * 查询一个居民的所有历史签约信息
	 * @param map
	 * @return
	 */
	public JSONObject querySingleUserQyxxAll(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer sqlBuf = new StringBuffer();
		
		sqlBuf.append("select b.QYSJ,b.QYLX,b.SXSJ,b.JSSJ QYJSSJ,c.*,d.YLJGMC QYJG,e.TDMC QYTD \n");
		sqlBuf.append("from TB_JTQY_JMXX a left join TB_JTQY_QYXX b on a.JMXXID = b.JMXXID \n");
		sqlBuf.append("left join TB_ZD_YLJGXX d on d.PTJGID=b.YLJGID \n");
		//c表
		sqlBuf.append("left join \n");
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("	  ( select  a.QYXXID , GROUP_CONCAT(b.FWBMC) QYFWB from  TB_JTQY_FWBGX a \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("	  ( select  a.QYXXID , LISTAGG(b.FWBMC,',') WITHIN GROUP(order by null) QYFWB from  TB_JTQY_FWBGX a \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		sqlBuf.append("     left join TB_XXWH_FWBJBXX b on a.FWBID = b.FWBID \n");
		sqlBuf.append("     group by a.QYXXID ) c \n");
		
		sqlBuf.append("on b.QYXXID = c.QYXXID ");
		sqlBuf.append("left join TB_TDGL_TDJBXX e on e.TDID=b.YLTDID where 1=1 \n");
		
		
		List<String> sqlParams = new ArrayList<String>();
		
		//包含的签约类型
		String qylxIn = map.get("qylxIn");
		if(!StringUtils.isEmpty(qylxIn)){
			String[] qylxNotInArr = qylxIn.split(",");
			sqlBuf.append("and b.QYLX in(");
			for (String qylx : qylxNotInArr) {
				sqlBuf.append("?,");
				sqlParams.add(qylx);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
		}
		
		//居民信息id
		String value = map.get("JMXXID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.JMXXID = ? ");
			sqlParams.add(value);
		}
		//证件号码
		value = map.get("ZJHM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.ZJHM = ? ");
			sqlParams.add(value);
		}
		
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		JSONObject result = new JSONObject();
		result.put("rows", super.queryList(bean));
		return result;
		
	}
	
	/**
	 * 分页查询一个居民历史签约信息到健康城市系统
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryQyHistoryByPageToJkcs(Map<String, String> map) {
		ControllerCommonUtil.pageFormat(map);
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer sqlBuf = new StringBuffer();
		
		sqlBuf.append("select b.QYSJ,b.QYLX,G.XMMC QYLXMC , b.SXSJ,b.JSSJ QYJSSJ,c.*,d.YLJGMC QYJG,e.*,h.DZXM \n");
		sqlBuf.append("from TB_JTQY_JMXX a left join TB_JTQY_QYXX b on a.JMXXID = b.JMXXID \n");
		sqlBuf.append("left join TB_ZD_YLJGXX d on d.PTJGID=b.YLJGID \n");
		sqlBuf.append("left join \n");
		//c表
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("(select t.QYXXID,group_concat(t.FWMC) FWMCS from ( select aa.QYXXID , cc.FWMC from  TB_JTQY_FWBGX aa \n");
			sqlBuf.append("left join TB_XXWH_FWB_FWXX bb on aa.FWBID = bb.FWBID ");
			sqlBuf.append("left join TB_XXWH_FWXX cc on bb.FWID = cc.FWID  ) t group by t.QYXXID ) c ");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("(select t.QYXXID,listagg(t.FWMC,',') within group(order by null) FWMCS from ( select aa.QYXXID , cc.FWMC from  TB_JTQY_FWBGX aa \n");
			sqlBuf.append("left join TB_XXWH_FWB_FWXX bb on aa.FWBID = bb.FWBID ");
			sqlBuf.append("left join TB_XXWH_FWXX cc on bb.FWID = cc.FWID  ) t group by t.QYXXID ) c ");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}

		
		sqlBuf.append("on b.QYXXID = c.QYXXID ");
		sqlBuf.append("left join TB_TDGL_TDJBXX e on e.TDID=b.YLTDID \n");
		String USERID = map.get("USERID");
		if(!StringUtils.isEmpty(USERID)){
			sqlBuf.append("left join TB_TDGL_TDRY f on f.TDID=b.YLTDID \n");
		}
		sqlBuf.append(" left join TB_ZY_JTQYZT G ON B.QYLX = G.XMDM \n");
		sqlBuf.append("left join (select gg.XM DZXM,ff.TDID from TB_TDGL_TDRY ff left join TB_ZD_YLJGRYXX gg on ff.USERID=gg.USERID where ff.TDJS=1) h on h.TDID=b.YLTDID ");
		sqlBuf.append("where 1=1 ");
		
		List<String> sqlParams = new ArrayList<String>();
		if(!StringUtils.isEmpty(USERID)){
			sqlBuf.append("and f.USERID = ? ");
			sqlParams.add(USERID);
		}
		//包含的签约类型
		String qylxIn = map.get("QYLXIN");
		if(!StringUtils.isEmpty(qylxIn)){
			String[] qylxNotInArr = qylxIn.split(",");
			sqlBuf.append("and b.QYLX in(");
			for (String qylx : qylxNotInArr) {
				sqlBuf.append("?,");
				sqlParams.add(qylx);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
		}
		
		//居民信息id
		String value = map.get("JMXXID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.JMXXID = ? ");
			sqlParams.add(value);
		}
		//证件号码
		value = map.get("ZJHM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.ZJHM = ? ");
			sqlParams.add(value);
		}


		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlBuf.toString(), "order by b.YLJGID,b.QYSJ desc", sqlParams.toArray());
		
		JSONObject result = super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
		
		//签约类型  --格式化
		//JSONArray rows = result.getJSONArray("rows");
		//JSONObject qylxObj = JSONObject.parseObject("{'00':'预签约','01':'已签约','02':'转介中','03':'已解约','04':'已到期','05':'续约','06':'拒签',}");
		//for(int i=0;i<rows.size();i++){
		//	JSONObject item = rows.getJSONObject(i);
		//	item.put("QYLXMC", qylxObj.get(item.get("QYLX")));
		//}

		return result;
		
	}

	/**
	 * 根据医生ID查询该医生签约的患者总数
	 * @param map {USERID:xxx}
	 * @return
	 */
	@Override
	public JSONObject queryQyzsToJkcs(Map<String,String> map){
		StringBuffer sqlBuf = new StringBuffer();
		//包括：已签约、转接中、已解约、已到期
		sqlBuf.append("select count(distinct ZJHM) QYZS from v_jtqy_qyxx where (QYLX='01' or QYLX='02' or QYLX='03' or QYLX='04') \n");
		sqlBuf.append("and USID = ? ");
		SQLBean bean = new SQLBean(sqlBuf.toString(), map.get("USERID"));
		return super.queryOne(bean);
	}

	/**
	 * 根据签约信息ids查询用于打印协议的数据
	 * @param map
	 * @return
	 */
//	@Override
//	public JSONArray queryPrintXyByQyxxids(Map<String, String> map) {
//		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
//		StringBuffer sqlBuf = new StringBuffer();
//		sqlBuf.append("select a.QYFY,b.XM,b.CSRQ,b.ZJHM,b.JZKH,b.SJHM,b.JTZZ,b.JHRXM,b.YJFGX,b.LXDH,b.SFZH,c.TDMC,c.LXDH TDDH,");
//		sqlBuf.append("(select XMMC from TB_ZY_XB where XMDM=b.XB) XB,"); // 患者性别
//		sqlBuf.append("(select XM from TB_ZD_YLJGRYXX where USERID=a.USID) JLYS, \n"); // 记录医生
//		sqlBuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID=c.YLJGID) YLJGMC, \n"); // 医疗机构名称
//		// 服务包名称
//		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
//			sqlBuf.append("(select GROUP_CONCAT(concat(f.FWBMC,case when e.FWBSX=1 then '(个人)' else '(家庭)' end)) from TB_JTQY_FWBGX e,TB_XXWH_FWBJBXX f where e.FWBID=f.FWBID and e.QYXXID=a.QYXXID) FWBMC \n");
//		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
//			sqlBuf.append("(select LISTAGG(concat(f.FWBMC,case when e.FWBSX=1 then '(个人)' else '(家庭)' end),',') WITHIN GROUP(order by null) from TB_JTQY_FWBGX e,TB_XXWH_FWBJBXX f where e.FWBID=f.FWBID and e.QYXXID=a.QYXXID) FWBMC \n");
//		}else{
//			throw new RuntimeException("无法确定分组函数");
//		}
//
//		sqlBuf.append("from TB_JTQY_QYXX a left join TB_JTQY_JMXX b on a.JMXXID=b.JMXXID \n");
//		sqlBuf.append("left join TB_TDGL_TDJBXX c on a.YLTDID=c.TDID where 1=1 \n");
//
//		String qyxxids = map.get("QYXXIDS");
//		String[] qyxxidArr = qyxxids.split(",");
//
//		List<String> sqlParams = new ArrayList<String>();
//		sqlBuf.append("and a.QYXXID in(");
//		for (String qyxxid : qyxxidArr) {
//			sqlBuf.append("?,");
//			sqlParams.add(qyxxid);
//		}
//		sqlBuf.deleteCharAt(sqlBuf.length()-1).append(")");
//
//		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
//		return super.queryList(bean);
//	}

	@Override
	public JSONObject findYljgxxByUserId(Map<String, String> map) {
		StringBuffer sb = new StringBuffer("SELECT PTJGID,USERID,XM,YLJGMC,JGXZQHDM,JGXZQHMC,SJHM FROM VIEW_YLJGRYXX WHERE USERID = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString(),new Object[]{map.get("USERID")});
		return this.queryOne(sqlBean);
	}

	@Override
    public JSONObject isExistYs(Map<String, String> map) {
		StringBuffer sb = new StringBuffer("SELECT a.PTJGID,a.USERID,b.XZQHDM FROM TB_ZD_YLJGRYXX a left join TB_ZD_YLJGXX b on a.PTJGID=b.PTJGID WHERE a.YLJGDM = ? AND a.SFZH = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString(),new Object[]{map.get("yljgdm"),map.get("sfzh")});
		return this.queryOne(sqlBean);
    }

	/*@Override
	public JSONObject isExistHz(Map<String, String> map) {
		StringBuffer sb = new StringBuffer("SELECT A.JMXXID,B.QYXXID, ");
		//签约的XZQHDM
		sb.append("(select aa.XZQHDM from TB_ZD_YLJGXX aa left join TB_ZD_YLJGRYXX bb on aa.PTJGID=bb.PTJGID where bb.USERID=B.USID) XZQHDM \n");
		sb.append("FROM TB_JTQY_JMXX A LEFT JOIN TB_JTQY_QYXX B ON A.JMXXID = B.JMXXID AND (B.QYLX= '01' OR B.QYLX = '02') \n");
		sb.append(" WHERE ZJHM = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString(), new Object[]{map.get("sfzh")});
		return this.queryOne(sqlBean);
	}*/
	/**
	 * 通过定时任务修改“签约类型”
	 * @param map
	 * @return
	 */
	public JSONArray updateQylxByTimedask(Map<String,String> map){
		//获取yyyy-MM-dd格式的日期
		Date nowDate = FormatDate.getDateY_M_D(FormatDate.getDateYYYY_MM_DD(new Date()));
		//测试
		//Date nowDate = FormatDate.getDateY_M_D("1904-10-11");

		//状态说明：00：预签约 01：已签约  02：转介中  03：已解约  04：已到期 05：已续约

		//step 1 : 已到期且未续约的进行自动续约
		JSONArray xyfwArr = this.autoXyfwOnTimeover(nowDate);

		//step 2 : 到期记录处理(标记为到期)
		StringBuffer sqlBufDq = new StringBuffer();
		sqlBufDq.append("update TB_JTQY_QYXX set QYLX = '04' where QYXXID in");
		//将02（转介中）记录加入：为了避免下一条续约记录启用后再处理的转介记录出现一个人2条（转介处理当天）正式签约记录
		sqlBufDq.append("(select QYXXID from TB_JTQY_QYXX where (QYLX = '01' or QYLX='05' or QYLX='02') and JSSJ < ?)");
		SQLBean beanDq = new SQLBean(sqlBufDq.toString(), nowDate);
		super.update(beanDq);

		//step 3 : 续约记录处理(标记为正常)
		StringBuffer sqlBufXy = new StringBuffer();
		sqlBufXy.append("update TB_JTQY_QYXX set QYLX = '01' where QYXXID in");
		sqlBufXy.append("(select QYXXID from TB_JTQY_QYXX where 1=1 \n");
		//只能当前没有正常的签约记录再启用续约记录（即：以上一条记录为准，只有上一条记录失效后才能签约）
		sqlBufXy.append("and JMXXID not in(select JMXXID from TB_JTQY_QYXX where qylx='01' )) \n");
		sqlBufXy.append("and sxsj <= ? and QYLX = '05'");
		SQLBean beanXy = new SQLBean(sqlBufXy.toString(), nowDate);
		super.update(beanXy);

		return xyfwArr;
	}

	/**
	 * 自动添加续约服务记录--在签约日期到期后进行
	 * ## 定时任务
	 * @param nowDate
	 */
	private JSONArray autoXyfwOnTimeover(Date nowDate){
		//根据查询结果入库 Demo
//		insert into fmttxt_item(proj_code,tradecode,fmt_code,itemno)
//		Select proj_code,tradecode,'3333',itemno from fmttxt_item
//		where fmt_code='3303'
		JSONArray result = new JSONArray();

		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());

		//step 1 : 查询已到期同时未续约的记录的QYXXID,服务包总费用(c.FY)要为0,即是：免费咯
		StringBuffer sqlWxy = new StringBuffer();
		//事件表需要的字段
		sqlWxy.append("select QYXXID,JMXXID,YLJGID PTJGID,YLJGMC,YLTDID TDID,TDMC,USID YSID,QYYS YSXM, \n");
		//协议模板中需要的字段
		sqlWxy.append("FWBID,XM,case when XB=1 then '男' else '女' end as XB,ZJHM,CSRQ,JZKH,SJHM,JTZZ,JHRXM,YJFGX,LXDH,SFZH,QYYS YFXM,");
		sqlWxy.append("TDLXDH YFSJHM,QYTDMC,YLJGMC YFYLJGMC,FWBMC,QYSJ XYSJ,'自动续约' CZRY,SXSJ,JSSJ,XZQHDM,XZQHMC JGXZQHMC ");
		sqlWxy.append("from V_JTQY_QYXX where (QYLX = '01' or QYLX='02') and JSSJ < ? and SFXY=0 and ZFY=0");
		SQLBean beanWxy = new SQLBean(sqlWxy.toString(),nowDate);
		JSONArray wxyArr = super.queryList(beanWxy);

		if(wxyArr.size() == 0){
			return result; //没有符合条件的数据---中断操作
		}

		//step 2 : 签约信息表插入续约记录
		StringBuffer sqlQyxxInster = new StringBuffer("insert into ").append(this.tb.getTableName());
		sqlQyxxInster.append("(QYXXID,JMXXID,YLTDID,YLJGID,QYSJ,SXSJ,JSSJ,USID,QYLX,SFXY,QYFY,SFYX,SFBS) ");
		sqlQyxxInster.append("select ");

		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			//mysql数据库
			//sysdate()==签约时间;  date_add(JSSJ,interval 1 day)==生效时间; date_add(JSSJ,interval 1 year)==结束时间
			sqlQyxxInster.append("?,JMXXID,YLTDID,YLJGID,sysdate(),date_add(JSSJ,interval 1 day),date_add(JSSJ,interval 1 year),USID,'01','0','0','0','0' ");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			//oracle数据库
			//sysdate==签约时间;  JSSJ+1==生效时间; add_month(JSSJ,12)==结束时间
			sqlQyxxInster.append("?,JMXXID,YLTDID,YLJGID,sysdate,JSSJ+1,add_months(JSSJ,12),USID,'01','0','0','0','0' ");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}
		sqlQyxxInster.append(" from ").append(this.tb.getTableName());
		sqlQyxxInster.append(" where QYXXID = ? ");

		List sqlParamsIn = new ArrayList();
		List<String> sqlParamsUp = new ArrayList<String>();
		for(int i=0;i<wxyArr.size();i++){
			String newQyxxid = UUID.randomUUID().toString();
			JSONObject item = wxyArr.getJSONObject(i);
			String oldQyxxid = item.getString("QYXXID");
			sqlParamsIn.add(new Object[]{newQyxxid, oldQyxxid});
			sqlParamsUp.add(oldQyxxid);

			item.put("QYXXID", newQyxxid);//重置返回的QYXXID
			result.add(item);
		}
		SQLBean beanQyxxInsert = new SQLBean(sqlQyxxInster.toString(),sqlParamsIn.toArray());
		beanQyxxInsert.setBatch(true);
		System.out.print(sqlQyxxInster.toString());
		super.update(beanQyxxInsert);

		//step 3: 签约服务包关系表插入数据(一对多)
		StringBuffer sqlFwbInsert = new StringBuffer();
		sqlFwbInsert.append("insert into TB_JTQY_FWBGX(FWBID,QYXXID,FWBSX) ");
		sqlFwbInsert.append("select FWBID,?,FWBSX from TB_JTQY_FWBGX where QYXXID = ?");
		SQLBean beanFwbInster = new SQLBean(sqlFwbInsert.toString(), sqlParamsIn.toArray());
		beanFwbInster.setBatch(true);
		super.update(beanFwbInster);

		//step 4 : 原签约信息记录的SFXY字段改为1
		StringBuffer sqlQyxxUpdate = new StringBuffer("update ").append(this.tb.getTableName()).append(" set SFXY=1 where QYXXID = ?");
		SQLBean beanQyxxUpdate = new SQLBean(sqlQyxxUpdate.toString(), sqlParamsUp.toArray());
		super.update(beanQyxxUpdate);

		//step 5: 医保服务项关系表插入数据(一对多)
		StringBuffer sqlYbfwxInsert = new StringBuffer();
		sqlYbfwxInsert.append("insert into TB_JTQY_YBFWXGX(FWID,QYXXID,DJ,FWCS,ZJ) ");
		sqlYbfwxInsert.append("select FWID,?,DJ,FWCS,ZJ from TB_JTQY_YBFWXGX where QYXXID = ?");
		SQLBean beanYbfwxInster = new SQLBean(sqlYbfwxInsert.toString(), sqlParamsIn.toArray());
		beanYbfwxInster.setBatch(true);
		super.update(beanYbfwxInster);

		return result;
	}

	@Override
	public JSONObject getQyxxInfo(String sfzh) {
		// 03 : 已解约
		StringBuffer sb = new StringBuffer("SELECT QYLX QYZT , YLJGMC QYJG , QYSJ , YLJGDM QYJGDM  , QYYS , TDMC QYTD , JSSJ DQSJ , RQFLMC RQFL , FWBMC QYFWB,JYSJ \n");
		sb.append(" FROM V_JTQY_QYXX WHERE SFYX = '0' AND (QYLX = '01' OR QYLX = '02' or QYLX = '03') AND ZJHM = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString() , sfzh);
		return super.queryOne(sqlBean);
	}

	@Override
	public JSONArray getQyfyxxInfo(String sfzh) {
		StringBuffer sb = new StringBuffer("SELECT QYLX QYZT , YLJGMC QYJG , SFBS , YLJGDM QYJGDM  , QYYS , TDMC QYTD , QYXXID , XM , FWBXX \n");
		sb.append(" FROM V_JTQY_QYXX WHERE SFYX = '0' AND (QYLX = '01' OR QYLX = '02' OR QYLX = '05') AND ZJHM = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString() , sfzh);
		JSONArray ary = super.queryList(sqlBean);
		if(ary.size() > 0){
			//服务项查询sql
			StringBuffer sqlFwx = new StringBuffer();
			sqlFwx.append("select b.FWID,b.FWMC,b.FWNR,(select XMMC from TB_ZY_SFLX where XMDM=b.SFLX) SFLX,b.SFBZ ")
				.append("from  (select FWID from TB_XXWH_FWB_FWXX where FWBID=?) a \n")
				.append("left join TB_XXWH_FWXX b on a.FWID=b.FWID ");

			for(int i = 0 ; i < ary.size() ; i ++){
				JSONObject item = ary.getJSONObject(i);
				String fwbxxStr = (String)item.remove("FWBXX");
				String[] fwbxxAry = fwbxxStr.split(",");
				int zfy = 0;
				JSONArray fwbAry = new JSONArray();
				for(String fwbxx : fwbxxAry){
					JSONObject fwb = new JSONObject();
					String[] fwbmx = fwbxx.split("\\|");
					fwb.put("FWBBH" , fwbmx[0]); //服务包ID
					fwb.put("FWBMC" , fwbmx[1]);
					fwb.put("FWBFY" , fwbmx[2]);
					fwbAry.add(fwb);
					zfy += Integer.parseInt(fwbmx[2]);

					//查询服务项
					SQLBean bean = new SQLBean(sqlFwx.toString(), fwbmx[0]);
					fwb.put("FWXX", super.queryList(bean));
				}
				item.put("FWBZFY" , zfy);
				item.put("FWBXX" , fwbAry);
			}
		}
		return ary;
	}

	@Override
	public boolean isExistSf(Map<String, String> map) {
		StringBuffer sb = new StringBuffer("SELECT COUNT(1) FROM V_JTQY_QYXX WHERE SFBS = '0' AND SFYX = '0' AND QYXXID = ? ");
		SQLBean sqlBean = new SQLBean(sb.toString() , map.get("QYXXID"));
		return super.isExist(sqlBean);
	}

	/**
	 * 根据签约时间段和证件号码查询包含或者包含于该时间段的签约信息，
	 * 用于判断患者是否已经签订免费包或基础包
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryQyxxByQysj(Map<String,String> map){
		Date SXSJ = FormatDate.getDateY_M_D(map.get("SXSJ"));
		Date JSSJ = FormatDate.getDateY_M_D(map.get("JSSJ"));

		List sqlParams = new ArrayList();
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.FWBID,a.YLTDID,a.FWBLX,a.QYLX,a.YLJGMC,a.TDMC,a.ZFY from V_JTQY_QYXX a where ");

		sqlBuf.append("(");
			// 1 : 生效期间包含其中
			sqlBuf.append("(a.SXSJ<=? and a.JSSJ>=?) ");
			sqlParams.add(SXSJ);
			sqlParams.add(SXSJ);

			// 2 : 结束期间包含其中
			sqlBuf.append("or (a.SXSJ<=? and a.JSSJ>=?) ");
			sqlParams.add(JSSJ);
			sqlParams.add(JSSJ);

			// 3 : 新的签约时间完全包含旧时间
			sqlBuf.append("or (a.SXSJ>=? and a.JSSJ<=?) ");
			sqlParams.add(SXSJ);
			sqlParams.add(JSSJ);
		sqlBuf.append(")");

		sqlBuf.append("and a.QYLX in('01','02','05') and a.ZJHM=? ");
		sqlParams.add(map.get("ZJHM"));

		//针对“更改服务包”业务
		String QYXXID = map.get("QYXXID");
		if(!StringUtils.isEmpty(QYXXID)){
			sqlBuf.append("and a.QYXXID <> ? ");
			sqlParams.add(QYXXID);
		}
		sqlBuf.append("order by a.QYSJ");

		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.queryList(bean);
	}

	/**
	 * 根据QYXXID与USERID判断医生是否是签约团队的团队成员
	 * @return
	 */
	public boolean isTdcyByQyxxAndUserid(Map<String,String> map){
		List sqlParams = new ArrayList();
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select count(1) from (select yltdid from tb_jtqy_qyxx where qyxxid=?) a \n");
		sqlBuf.append("left join tb_tdgl_tdry b on a.yltdid=b.tdid where b.USERID = ?");

		sqlParams.add(map.get("QYXXID"));
		sqlParams.add(map.get("USERID"));

		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.isExist(bean);
	}


}
